﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using SQLite2CELib;
using System.IO;
using Microsoft.CSharp;
using System.CodeDom.Compiler;
using Microsoft.Data.Schema.ScriptDom.Sql;
using Microsoft.Data.Schema.ScriptDom;

namespace SQLite2CEUI
{
    public partial class SqlToLinqConverter : Form
    {
        private const string _fileGenInProgress = "Please wait while the tool processes the database..!";
        private const string _fileGenSuccess = "Database processed successfully!";
        private const string _fileGenError = "Error processing database!";

        private string _assemblyLoc;

        public SqlToLinqConverter()
        {
            InitializeComponent();
            InitTempFileLocation();
        }

        private string FileStorageLoc
        {
            get
            {
                return Path.Combine(Path.GetTempPath(), "SqlToLinqConverter");
            }
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            if (chooseSQLiteFileDialog.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            txtSQLiteFilePath.Text = chooseSQLiteFileDialog.FileName;
        }

        private void btnSelect_Click(object sender, EventArgs e)
        {
            try
            {
                lblStatus.Text = String.Empty;
                _assemblyLoc = String.Empty;

                if (String.IsNullOrWhiteSpace(txtSQLiteFilePath.Text) == true)
                {
                    return;
                }
                lblStatus.Text = _fileGenInProgress;
                //MessageBox.Show("Please wait while tool creates necessary files..!");
                DeleteAllFilesInDir(FileStorageLoc);

                //Start converting SQLite db To SqlCe.
                SQLiteDbConverter.RunWorkerAsync();
            }
            catch (Exception ex)
            {
                lblStatus.Text = _fileGenError;
                MessageBox.Show("Error processing database - " + ex.Message);
            }
        }

        private void InitTempFileLocation()
        {
            if (Directory.Exists(FileStorageLoc) == false)
            {
                Directory.CreateDirectory(FileStorageLoc);
            }
            else
            {
                DeleteAllFilesInDir(FileStorageLoc);
            }
        }

        private void DeleteAllFilesInDir(String dirLocation)
        {
            String[] filesInDir;

            filesInDir = Directory.GetFiles(dirLocation);
            if (filesInDir != null && filesInDir.Count() != 0)
            {
                foreach (String filePath in filesInDir)
                {
                    try
                    {
                        File.Delete(filePath);
                    }
                    catch (UnauthorizedAccessException)
                    {
                        //Ignore the files in use.
                    }
                }
            }
        }

        private String GenerateAssemblyFromSqlMetalClasses(string sqlMetalClassLocation)
        {
            String nameOfAssembly;
            CSharpCodeProvider codeProvider;
            CompilerParameters compilerParams;
            CompilerResults compilerResult;

            codeProvider = new CSharpCodeProvider();
            compilerParams = new CompilerParameters();
            compilerParams.ReferencedAssemblies.Add("System.dll");
            compilerParams.ReferencedAssemblies.Add("System.Data.dll");
            compilerParams.ReferencedAssemblies.Add("System.Core.dll");
            compilerParams.ReferencedAssemblies.Add("System.Data.DataSetExtensions.dll");
            compilerParams.ReferencedAssemblies.Add("System.Data.Linq.dll");

            compilerParams.GenerateExecutable = false;
            compilerParams.GenerateInMemory = false;

            nameOfAssembly = String.Concat(Guid.NewGuid().ToString() + ".dll");

            //Store the dll for sql metal at temporary location.
            compilerParams.OutputAssembly = Path.Combine(FileStorageLoc, nameOfAssembly);

            compilerResult = codeProvider.CompileAssemblyFromFile(compilerParams, new String[] { sqlMetalClassLocation });
            return compilerParams.OutputAssembly;
        }

        private void btnConvert_Click(object sender, EventArgs e)
        {
            if (String.IsNullOrWhiteSpace(_assemblyLoc) == true)
            {
                MessageBox.Show("Please configure the database before coverting SQL Query to LINQ.", "Error");
                return;
            }
            TSql100Parser parser;
            bool fQuotedIdenfifiers = false;
            TableSchemaHelper tableSchemaHelper;
            GeneratedLinqInfo linqInfo;
            Errors conversionErrors;
            TSqlScript script;
            IScriptFragment fragment;
            IList<ParseError> errors;

            try
            {
                txtLINQQuery.Text = String.Empty;

                parser = new TSql100Parser(fQuotedIdenfifiers);
                fragment = parser.Parse(new StringReader(txtSqlQuery.Text), out errors);

                tableSchemaHelper = new TableSchemaHelper();
                tableSchemaHelper.Initialize(_assemblyLoc);

                script = fragment as TSqlScript;

                foreach (TSqlBatch batch in script.Batches)
                {
                    foreach (TSqlStatement statement in batch.Statements)
                    {
                        if (statement is SelectStatement)
                        {
                            BuildLinqForSelectStatement linqBuilder = new BuildLinqForSelectStatement();
                            linqInfo = linqBuilder.GenerateLinq(statement as SelectStatement, tableSchemaHelper, out conversionErrors);

                            if (linqInfo != null)
                            {
                                txtLINQQuery.Text = linqInfo.Linq.ToString();
                            }
                            if (conversionErrors != null)
                            {
                                foreach (string errorMsg in conversionErrors.ErrorMessages)
                                {
                                    txtLINQQuery.AppendText(Environment.NewLine);
                                    txtLINQQuery.AppendText(errorMsg);
                                }
                            }
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                txtLINQQuery.Text = "Error in converting SQL Query. Details are - " + ex.Message;
            }
        }

        private void SQLiteDbConverter_DoWork(object sender, DoWorkEventArgs e)
        {
            String ceDbPath;
            Converter dbConverter;
            String sqliteDbFileNameWithoutExtn;

            try
            {
                sqliteDbFileNameWithoutExtn = Path.GetFileNameWithoutExtension(txtSQLiteFilePath.Text);
                ceDbPath = Path.Combine(FileStorageLoc, sqliteDbFileNameWithoutExtn + ".sdf");

                //Convert the SQLite db to SqlCe.
                dbConverter = new Converter();
                dbConverter.Init(false, true,
                                 txtSQLiteFilePath.Text, ceDbPath,
                                 String.Empty, String.Empty, true, true, false);
                dbConverter.Convert();

                //Create assembly that can be used for reflection.
                _assemblyLoc = GenerateAssemblyFromSqlMetalClasses(Path.Combine(FileStorageLoc, sqliteDbFileNameWithoutExtn + ".cs"));
                
                e.Result = true; //Set status of the operation to true because everything went well.
            }
            catch (Exception ex)
            {
                e.Result = false;
                MessageBox.Show("Error generating necessary files - " + ex.Message);   
            }
        }

        private void SQLiteDbConverter_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if ((bool)e.Result == true)
            {
                lblStatus.Text = _fileGenSuccess;
                tabSqlToLinqConverter.SelectedIndex = 1;
            }
            else
            {
                lblStatus.Text = _fileGenError;
            }
        }
    }
}
